import os
from typing import Union

from mysql.connector.abstracts import MySQLConnectionAbstract
from mysql.connector.pooling import PooledMySQLConnection
from pyecharts import options as opts
from pyecharts.charts import Tab, Page, Line, Bar, Pie, Map
from pyecharts.charts.base import Base
from pyecharts.charts.chart import Chart
from pyecharts.globals import ThemeType
from pyecharts.options import PageLayoutOpts

import dbs
import utils


class AbstractChartCreator(object):

    def __init__(self, title: str):
        self.title = title

    def create(self, conn: Union[PooledMySQLConnection, MySQLConnectionAbstract]) -> Union[Chart, Tab, Page]:
        raise Exception("子类实现")

    def save(self, conn: Union[PooledMySQLConnection, MySQLConnectionAbstract]):
        self.create(conn).render(f"../output_views{os.sep}{self.title}.html")


class AbstractComponentChartCreator(AbstractChartCreator):
    def __init__(self, title: str):
        super().__init__(title)
        self.children: list[AbstractChartCreator] = []

    def add(self, child: AbstractChartCreator):
        self.children.append(child)


class TabCreator(AbstractComponentChartCreator):

    def create(self, conn: Union[PooledMySQLConnection, MySQLConnectionAbstract]) -> Tab:
        tab = Tab(page_title=self.title)
        for c in self.children:
            tab.add(c.create(conn), c.title)
        return tab


class PageCreator(AbstractComponentChartCreator):

    def __init__(self, title: str, layout: PageLayoutOpts = Page.SimplePageLayout):
        super().__init__(title)
        self.layout: PageLayoutOpts = layout

    def create(self, conn: Union[PooledMySQLConnection, MySQLConnectionAbstract]) -> Page:
        page = Page(page_title=self.title, layout=self.layout)
        for c in self.children:
            page.add(c.create(conn))
        return page


# --------------------------------------------------------- 数据可视化 ---------------------------------------------------------

class DailyOrderCountCreator(AbstractChartCreator):
    def __init__(self):
        super().__init__("每日订单总量")

    def create(self, conn: Union[PooledMySQLConnection, MySQLConnectionAbstract]) -> Union[Chart, Tab, Page]:
        dates: list[str] = []
        mp: dict[str:int] = {}
        with conn.cursor() as cursor:
            cursor.execute("select * from daily_order_count")
            lt = cursor.fetchall()
            for t in lt:
                k = str(t[0])
                dates.append(k)
                mp[k] = int(t[1])
        x_data: list[str] = utils.fill_missing_dates(dates)
        y_data: list[int] = []
        for k in x_data:
            y_data.append(mp.get(k, 0))

        line = Line(init_opts=opts.InitOpts(theme=ThemeType.ROMANTIC))
        line.add_xaxis(xaxis_data=x_data)
        line.add_yaxis(series_name=self.title, y_axis=y_data, is_smooth=True,
                       areastyle_opts=opts.AreaStyleOpts(opacity=0.5), linestyle_opts=opts.LineStyleOpts(width=2),
                       label_opts=opts.LabelOpts(is_show=False), )
        line.set_global_opts(tooltip_opts=opts.TooltipOpts(trigger="axis", axis_pointer_type="cross"),
                             xaxis_opts=opts.AxisOpts(type_="category", boundary_gap=False),
                             yaxis_opts=opts.AxisOpts(type_="value"),
                             visualmap_opts=opts.VisualMapOpts(max_=200000, is_piecewise=True))
        return line


class DailyOrderCompleteCountCreator(AbstractChartCreator):
    def __init__(self):
        super().__init__("每日订单完成量")

    def create(self, conn: Union[PooledMySQLConnection, MySQLConnectionAbstract]) -> Union[Chart, Tab, Page]:
        dates: list[str] = []
        mp: dict[str:int] = {}
        with conn.cursor() as cursor:
            cursor.execute("select * from daily_completed_order_count")
            lt = cursor.fetchall()
            for t in lt:
                k = str(t[0])
                dates.append(k)
                mp[k] = int(t[1])
        x_data: list[str] = utils.fill_missing_dates(dates)
        y_data: list[int] = []
        for k in x_data:
            y_data.append(mp.get(k, 0))

        line = Line(init_opts=opts.InitOpts(theme=ThemeType.ROMANTIC))
        line.add_xaxis(xaxis_data=x_data)
        line.add_yaxis(series_name=self.title, y_axis=y_data, is_smooth=True, symbol="circle", symbol_size=6,
                       linestyle_opts=opts.LineStyleOpts(width=2), label_opts=opts.LabelOpts(is_show=False),
                       itemstyle_opts=opts.ItemStyleOpts(border_width=1, border_color="#FFFFFF", color="#FAC858"),
                       markpoint_opts=opts.MarkPointOpts(data=[opts.MarkPointItem(type_="max", name="最大值"),
                                                               opts.MarkPointItem(type_="min", name="最小值")]))
        line.set_global_opts(tooltip_opts=opts.TooltipOpts(trigger="axis", axis_pointer_type="cross"),
                             xaxis_opts=opts.AxisOpts(type_="category", boundary_gap=False),
                             yaxis_opts=opts.AxisOpts(type_="value"),
                             legend_opts=opts.LegendOpts(orient="vertical", pos_right="50%", pos_top="0%"))
        return line


class DailyReturnedOrderCountCreator(AbstractChartCreator):
    def __init__(self):
        super().__init__(title="每日退货数量")

    def create(self, conn: Union[PooledMySQLConnection, MySQLConnectionAbstract]) -> Union[Chart, Tab, Page]:
        dates: list[str] = []
        mp: dict[str:int] = {}
        with conn.cursor() as cursor:
            cursor.execute("select * from daily_returned_order_count")
            lt = cursor.fetchall()
            for t in lt:
                k = str(t[0])
                dates.append(k)
                mp[k] = int(t[1])
        x_data: list[str] = utils.fill_missing_dates(dates)
        y_data: list[int] = []
        for k in x_data:
            y_data.append(mp.get(k, 0))
        line = Line(init_opts=opts.InitOpts(theme=ThemeType.ESSOS))
        line.add_xaxis(xaxis_data=x_data)
        line.add_yaxis(series_name=self.title, y_axis=y_data, is_smooth=True,  # 平滑曲线
                       linestyle_opts=opts.LineStyleOpts(width=3, type_="dotted"),  # 点线样式
                       label_opts=opts.LabelOpts(is_show=True, position="top", color="black"),  # 显示顶部标签
                       markline_opts=opts.MarkLineOpts(data=[opts.MarkLineItem(type_="average", name="平均值")]
                                                       # 添加平均值标线
                                                       ))
        line.set_global_opts(tooltip_opts=opts.TooltipOpts(trigger="axis", axis_pointer_type="cross"),
                             xaxis_opts=opts.AxisOpts(type_="category", boundary_gap=False),
                             yaxis_opts=opts.AxisOpts(type_="value"),
                             legend_opts=opts.LegendOpts(orient="vertical", pos_right="50%", pos_top="0%"))

        return line


class DailyReturnedRateCreator(AbstractChartCreator):
    def __init__(self):
        super().__init__(title="每日退货率")

    def create(self, conn: Union[PooledMySQLConnection, MySQLConnectionAbstract]) -> Union[Chart, Tab, Page]:
        dates: list[str] = []
        mp: dict[str:int] = {}
        with conn.cursor() as cursor:
            cursor.execute("select * from daily_return_rate")
            lt = cursor.fetchall()
            for t in lt:
                k = str(t[0])
                dates.append(k)
                mp[k] = round(float(t[1]), 4)
        x_data: list[str] = utils.fill_missing_dates(dates)
        y_data: list[float] = []
        for k in x_data:
            y_data.append(mp.get(k, 0))
        line = Line(init_opts=opts.InitOpts(theme=ThemeType.ESSOS))
        line.add_xaxis(xaxis_data=x_data)
        line.add_yaxis(series_name=self.title, y_axis=y_data, is_smooth=True,
                       linestyle_opts=opts.LineStyleOpts(width=3, type_="dotted"),
                       label_opts=opts.LabelOpts(is_show=True, position="top", color="black"),
                       markline_opts=opts.MarkLineOpts(data=[opts.MarkLineItem(type_="average", name="平均值")]))
        line.set_global_opts(tooltip_opts=opts.TooltipOpts(trigger="axis", axis_pointer_type="cross"),
                             xaxis_opts=opts.AxisOpts(type_="category", boundary_gap=False),
                             yaxis_opts=opts.AxisOpts(type_="value"),
                             legend_opts=opts.LegendOpts(orient="vertical", pos_right="50%", pos_top="0%"))
        return line


class ModelSalesCountCreator(AbstractChartCreator):
    def __init__(self):
        super().__init__("销量")

    def create(self, conn: Union[PooledMySQLConnection, MySQLConnectionAbstract]) -> Union[Chart, Tab, Page]:
        x_data: list[str] = []
        y_data: list[int] = []
        with conn.cursor() as cursor:
            cursor.execute("select * from model_sales_count")
            lt = cursor.fetchall()
            for t in lt:
                x_data.append(str(t[0]).replace("vivo", ""))
                y_data.append(int(t[1]))
        bar = Bar(init_opts=opts.InitOpts(theme=ThemeType.SHINE, width="1800px", height="600px"))
        bar.add_xaxis(xaxis_data=x_data)
        bar.add_yaxis(self.title, y_axis=y_data, label_opts=opts.LabelOpts(is_show=False))
        bar.set_global_opts(xaxis_opts=opts.AxisOpts(axislabel_opts=opts.LabelOpts(rotate=-45)),
                            yaxis_opts=opts.AxisOpts(name="数量", axislabel_opts=opts.LabelOpts()),
                            tooltip_opts=opts.TooltipOpts())
        return bar


# average_price
class AveragePriceCreator(AbstractChartCreator):
    def __init__(self):
        super().__init__("平均售价")

    def create(self, conn: Union[PooledMySQLConnection, MySQLConnectionAbstract]) -> Union[Chart, Tab, Page]:
        x_data: list[str] = []
        y_data: list[int] = []
        with conn.cursor() as cursor:
            cursor.execute("select * from average_price")
            lt = cursor.fetchall()
            for t in lt:
                x_data.append(str(t[0]).replace("vivo", ""))
                y_data.append(round(float(t[1]), 2))
        bar = Bar(init_opts=opts.InitOpts(theme=ThemeType.LIGHT, width="1800px", height="600px"))
        bar.add_xaxis(x_data)
        bar.add_yaxis(self.title, y_data, label_opts=opts.LabelOpts(is_show=False))
        bar.set_series_opts(label_opts=opts.LabelOpts(is_show=True, position="top"))
        bar.set_global_opts(xaxis_opts=opts.AxisOpts(axislabel_opts=opts.LabelOpts(rotate=-45)),
                            yaxis_opts=opts.AxisOpts(name="售价", axislabel_opts=opts.LabelOpts()),
                            tooltip_opts=opts.TooltipOpts())
        return bar


class MemberOrderCountCreator(AbstractChartCreator):
    def __init__(self):
        super().__init__("Plus会员与非Plus会员购买量对比")

    def create(self, conn: Union[PooledMySQLConnection, MySQLConnectionAbstract]) -> Union[Chart, Tab, Page]:
        data: list = []
        with conn.cursor() as cursor:
            cursor.execute("select * from member_order_count")
            lt = cursor.fetchall()
            for t in lt:
                data.append((str(t[0]), int(t[1])))
        pie = Pie(init_opts=opts.InitOpts())
        pie.add(self.title, data)
        pie.set_global_opts(title_opts=opts.TitleOpts(title=self.title))
        pie.set_series_opts(label_opts=opts.LabelOpts(formatter="{b}: {c}"))
        return pie


class StudentOrderCountCreator(AbstractChartCreator):
    def __init__(self):
        super().__init__("是否是学生的购买量对比")

    def create(self, conn: Union[PooledMySQLConnection, MySQLConnectionAbstract]) -> Union[Chart, Tab, Page]:
        data: list = []
        with conn.cursor() as cursor:
            cursor.execute("select * from student_order_count")
            lt = cursor.fetchall()
            for t in lt:
                data.append((str(t[0]), int(t[1])))
        pie = Pie(init_opts=opts.InitOpts())
        pie.add(self.title, data)
        pie.set_global_opts(title_opts=opts.TitleOpts(title=self.title))
        pie.set_series_opts(label_opts=opts.LabelOpts(formatter="{b}: {c}"))
        return pie


class ProvinceOrderCountCreator(AbstractChartCreator):
    def __init__(self):
        super().__init__("各省订单销量热力图")

    def create(self, conn: Union[PooledMySQLConnection, MySQLConnectionAbstract]) -> Union[Chart, Tab, Page]:
        data = []
        with conn.cursor() as cursor:
            cursor.execute("select * from province_order_count")
            lt = cursor.fetchall()
            for t in lt:
                data.append((utils.fill_province(str(t[0])), int(t[1])))
        # pieces = [
        #     {"min": 100000, "label": '> 100000', "color": '#7f1818'},
        #     # 自定义分段的范围、标签和颜色
        #     {"min": 50000, "max": 99999, "label": '50000-99999',
        #      "color": '#b21f1f'},
        #     {"min": 20000, "max": 49999, "label": '20000-49999',
        #      "color": '#e25555'},
        #     {"min": 10000, "max": 19999, "label": '10000-19999',
        #      "color": '#f59e83'},
        #     {"min": 0, "max": 9999, "label": '0-9999', "color": '#fdebcf'},
        # ]
        map = Map(init_opts=opts.InitOpts(width="1800px", height="900px"))
        map.add("订单量", data, "china")
        map.set_global_opts(title_opts=opts.TitleOpts(title=self.title),
                            visualmap_opts=opts.VisualMapOpts(max_=200000, is_piecewise=True, split_number=20))
        return map


class CityOrderCountCreator(AbstractChartCreator):

    def __init__(self):
        super().__init__("城市订单量")

    def create(self, conn: Union[PooledMySQLConnection, MySQLConnectionAbstract]) -> Union[Chart, Tab, Page]:
        cities = []
        values = []
        filters = ['虚拟', '其他', '未知']
        with conn.cursor() as cursor:
            cursor.execute("select * from city_order_count")
            lt = cursor.fetchall()
            for t in lt:
                pron = str(t[0])
                if utils.contains(pron, filters):
                    continue
                cities.append(pron)
                values.append(int(t[1]))
        bar = Bar()
        bar.add_xaxis(cities)
        bar.add_yaxis("订单量", values)
        bar.set_series_opts(label_opts=opts.LabelOpts(position="top"))
        bar.set_global_opts(
            title_opts=opts.TitleOpts(title=self.title),
            yaxis_opts=opts.AxisOpts(axislabel_opts=opts.LabelOpts(formatter="{value}")),
            xaxis_opts=opts.AxisOpts(axislabel_opts=opts.LabelOpts(rotate=-15))
        )
        return bar


class ModelProvinceSalesCountCreator(AbstractChartCreator):

    def __init__(self):
        super().__init__("vivo在各省的销量分布")

    def create(self, conn: Union[PooledMySQLConnection, MySQLConnectionAbstract]) -> Union[Chart, Tab, Page]:
        mp = {}
        data = []
        filters = ['虚拟', '其他', '未知']
        with conn.cursor() as cursor:
            cursor.execute("select * from model_province_sales_count where dimension like 'vivo%';")
            lt = cursor.fetchall()
            for t in lt:
                pron = str(t[1])
                if utils.contains(pron, filters):
                    continue
                mp[pron] = mp.get(pron, 0) + int(t[2])
        for k, v in mp.items():
            data.append((utils.fill_province(k), v))
        data.sort(key=lambda x: -x[1])
        map = Map(init_opts=opts.InitOpts(width="1800px", height="900px", theme=ThemeType.LIGHT))
        map.add("", data, "china")
        map.set_global_opts(title_opts=opts.TitleOpts(title=self.title),
                            visualmap_opts=opts.VisualMapOpts(max_=200000, is_piecewise=True, split_number=20))
        return map


class AfterSalesCountCreator(AbstractChartCreator):
    def __init__(self):
        super().__init__("每日售后申请服务量")

    def create(self, conn: Union[PooledMySQLConnection, MySQLConnectionAbstract]) -> Union[Chart, Tab, Page]:
        x_data = []
        y_data = []
        with conn.cursor() as cursor:
            cursor.execute("select * from after_sales_count")
            lt = cursor.fetchall()
            for t in lt:
                x_data.append(str(t[0]))
                y_data.append(int(t[1]))

        line = Line(init_opts=opts.InitOpts(theme=ThemeType.SHINE, width="1800px", height="600px"))
        line.add_xaxis(xaxis_data=x_data)
        line.add_yaxis(series_name="服务量", y_axis=y_data, symbol="emptyCircle", is_symbol_show=True,
                       label_opts=opts.LabelOpts(is_show=True), )
        line.set_global_opts(title_opts=opts.TitleOpts(title=self.title), tooltip_opts=opts.TooltipOpts(is_show=True),
                             xaxis_opts=opts.AxisOpts(name='', name_location='middle', name_gap=30,
                                                      name_textstyle_opts=opts.TextStyleOpts(
                                                          font_family='Times New Roman', font_size=16)),
                             yaxis_opts=opts.AxisOpts(type_="value", axistick_opts=opts.AxisTickOpts(is_show=True),
                                                      splitline_opts=opts.SplitLineOpts(is_show=True), name='',
                                                      name_location='middle', name_gap=30,
                                                      name_textstyle_opts=opts.TextStyleOpts(
                                                          font_family='Times New Roman', font_size=16)))
        return line


class DailySalesSumCreator(AbstractChartCreator):

    def __init__(self):
        super().__init__("每日销售额")

    def create(self, conn: Union[PooledMySQLConnection, MySQLConnectionAbstract]) -> Union[Chart, Tab, Page]:
        dates: list[str] = []
        mp: dict[str:int] = {}
        with conn.cursor() as cursor:
            cursor.execute("select * from daily_sales_sum")
            lt = cursor.fetchall()
            for t in lt:
                k = str(t[0])
                dates.append(k)
                mp[k] = round(float(t[1]), 0)
        x_data: list[str] = utils.fill_missing_dates(dates)
        y_data: list[int] = []
        for k in x_data:
            y_data.append(mp.get(k, 0))
        line = Line(init_opts=opts.InitOpts())
        line.add_xaxis(x_data)
        line.add_yaxis("销售额", y_data, label_opts=opts.LabelOpts(is_show=False))
        line.set_global_opts(
            title_opts=opts.TitleOpts(title=self.title),
            tooltip_opts=opts.TooltipOpts(trigger="axis", axis_pointer_type="cross"),
            xaxis_opts=opts.AxisOpts(type_="category", boundary_gap=False),
            yaxis_opts=opts.AxisOpts(type_="value", name="销售额"),
        )
        return line


class OrderCategorySumCreator(AbstractChartCreator):
    def __init__(self):
        super().__init__("订单种类分布")

    def create(self, conn: Union[PooledMySQLConnection, MySQLConnectionAbstract]) -> Union[Chart, Tab, Page]:
        order_data = []
        with conn.cursor() as cursor:
            cursor.execute("select * from order_category_sum")
            lt = cursor.fetchall()
            for t in lt:
                order_data.append((str(t[0]), int(t[1])))
        pie = Pie(init_opts=opts.InitOpts())
        pie.add("", order_data)
        pie.set_global_opts(
            title_opts=opts.TitleOpts(title="订单种类分布"),
            legend_opts=opts.LegendOpts(orient="vertical", pos_left="right"),
        )
        pie.set_series_opts(label_opts=opts.LabelOpts(formatter="{b}: {c} ({d}%)"))
        return pie


# --------------------------------------------------------- 函数 ---------------------------------------------------------

_creators: list[AbstractChartCreator] = [
    DailyOrderCountCreator(),
    DailyOrderCompleteCountCreator(),
    DailyReturnedOrderCountCreator(),
    DailyReturnedRateCreator(),
    ModelSalesCountCreator(),
    AveragePriceCreator(),
    MemberOrderCountCreator(),
    StudentOrderCountCreator(),
    ProvinceOrderCountCreator(),
    CityOrderCountCreator(),
    ModelProvinceSalesCountCreator(),
    AfterSalesCountCreator(),
    DailySalesSumCreator(),
    OrderCategorySumCreator()
]


def get_page() -> AbstractChartCreator:
    page = PageCreator(title="订单大屏可视化")
    for c in _creators:
        page.add(c)
    return page


def get_chart(name: str) -> Base:
    conn = dbs.get_mysql_conn()
    if "page" == name:
        return get_page().create(conn)
    try:
        i = int(name)
        creator = _creators[i]
    except Exception as ex:
        raise ex
    return None if creator is None else creator.create(conn)


def generate_links():
    print('<a href="/visual/page" target="_blank">大屏可视化页面</a>')
    for i, c in enumerate(_creators):
        s = f"<a href='/visual/{i}' target='_blank'>{c.title}</a>"
        print(s)


if __name__ == '__main__':
    generate_links()
